package com.matt.repository.impl;

import com.matt.foundation.query.LeadsHistoryQuery;
import com.matt.foundation.vo.LeadsHistoryVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.LeadsHistoryNativeRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * ***************************
 * Created by Ron
 * from com.matt
 * on 2018/6/26 0026.
 * ***************************
 */
@Service
@Transactional
public class LeadsHistoryNativeRepositoryImpl extends BaseNativeSqlRepository implements LeadsHistoryNativeRepository {

    @Override
    public List<LeadsHistoryVo> findByLeadsIdAndType(String leadsId, String type, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select ch.id,ch.channel,ch.isPallet,ch.isVisit,ch.palletTime,ch.visitTime,ch.remark," +
                "ch.leadsStatus,ch.clienteleStatus,ch.type,ch.content,ch.actualFollowDate,ch.operationType as operationType," +
                "ch.nextTime,ch.imgUrl,ch.attachmentUrl,ch.leadsId,ch.clientelesId,ch.staffId,ch.staffHeadImgUrl,ch.staffName,le.channel as leadsChannel,le.fromUser as leadsfromUser" +
                " from leads_history as ch inner join leads as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId," and ch.type='%s'",type);

        querySql +=String.format(" order by ch.created desc");
        querySql +=String.format(" limit %s,%s ",leadsHistoryQuery.getPageNun() * leadsHistoryQuery.getPageSize(),leadsHistoryQuery.getPageSize());
        List<LeadsHistoryVo> list=sqlObjectList(querySql,LeadsHistoryVo.class);
        return list;
    }


    @Override
    public List<LeadsHistoryVo> findByVisit(String leadsId, String type, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select ch.id,ch.channel,ch.isPallet,ch.isVisit,ch.palletTime,ch.visitTime,ch.remark," +
                "ch.leadsStatus,ch.clienteleStatus,ch.type,ch.content,ch.actualFollowDate,ch.operationType as operationType," +
                "ch.nextTime,ch.imgUrl,ch.attachmentUrl,ch.leadsId,ch.clientelesId,ch.staffId,ch.staffHeadImgUrl,ch.staffName" +
                " from leads_history as ch inner join about_visit as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId," and ch.type='%s'",type);

        querySql +=String.format(" order by ch.created desc");
        querySql +=String.format(" limit %s,%s ",leadsHistoryQuery.getPageNun() * leadsHistoryQuery.getPageSize(),leadsHistoryQuery.getPageSize());
        List<LeadsHistoryVo> list=sqlObjectList(querySql,LeadsHistoryVo.class);
        return list;
    }


    @Override
    public Long visitcount(String leadsId,String type, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select count(ch.id) from leads_history as ch inner join about_visit as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId," and ch.type='%s'",type);
        List objectArrayList = sqlSingleList(querySql);
        return  Long.valueOf(objectArrayList.get(0).toString());
    }



    @Override
    public List<LeadsHistoryVo> findByPallet(String leadsId, String type, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select ch.id,ch.channel,ch.isPallet,ch.isVisit,ch.palletTime,ch.visitTime,ch.remark," +
                "ch.leadsStatus,ch.clienteleStatus,ch.type,ch.content,ch.actualFollowDate,ch.operationType as operationType," +
                "ch.nextTime,ch.imgUrl,ch.attachmentUrl,ch.leadsId,ch.clientelesId,ch.staffId,ch.staffHeadImgUrl,ch.staffName" +
                " from leads_history as ch inner join about_pallet as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId," and ch.type='%s'",type);

        querySql +=String.format(" order by ch.created desc");
        querySql +=String.format(" limit %s,%s ",leadsHistoryQuery.getPageNun() * leadsHistoryQuery.getPageSize(),leadsHistoryQuery.getPageSize());
        List<LeadsHistoryVo> list=sqlObjectList(querySql,LeadsHistoryVo.class);
        return list;
    }


    @Override
    public Long palletcount(String leadsId,String type, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select count(ch.id) from leads_history as ch inner join about_pallet as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId," and ch.type='%s'",type);
        List objectArrayList = sqlSingleList(querySql);
        return  Long.valueOf(objectArrayList.get(0).toString());
    }





    @Override
    public Long count(String leadsId,String type, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select count(ch.id) from leads_history as ch inner join leads as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId," and ch.type='%s'",type);
        List objectArrayList = sqlSingleList(querySql);
        return  Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<LeadsHistoryVo> findByClienteledIdAndType(String clientelesId, String type, LeadsHistoryQuery query) {
        String querySql=String.format("select ch.id,ch.channel,ch.isPallet,ch.isVisit,ch.palletTime,ch.visitTime,ch.remark,ch.leadsStatus,ch.clienteleStatus,ch.type,ch.content,ch.actualFollowDate,ch.operationType as operationType," +
                "ch.nextTime,ch.imgUrl,ch.attachmentUrl,ch.leadsId,ch.clientelesId,ch.staffId,ch.staffHeadImgUrl,ch.staffName,le.channel as leadsChannel,le.fromUser as leadsfromUser" +
                " from leads_history as ch inner join leads as le on ch.leadsId=le.id where ch.clientelesId='%s'",clientelesId," and ch.type='%s'",type);

        querySql +=String.format(" order by ch.created desc");
        querySql +=String.format(" limit %s,%s ",query.getPageNun() * query.getPageSize(),query.getPageSize());
        List<LeadsHistoryVo> list=sqlObjectList(querySql,LeadsHistoryVo.class);
        return list;
    }

    @Override
    public Long countByClienteleId(String clientelesId,String type, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select count(ch.id) from leads_history as ch inner join leads as le on ch.leadsId=le.id where ch.clientelesId='%s'",clientelesId," and ch.type='%s'",type);
        List objectArrayList = sqlSingleList(querySql);
        return  Long.valueOf(objectArrayList.get(0).toString());
    }

    @Override
    public List<LeadsHistoryVo> findAll(String leadsId, LeadsHistoryQuery query) {
        String querySql=String.format("select ch.id,ch.channel,ch.leadsStatus,ch.isPallet,ch.isVisit,ch.palletTime,ch.visitTime,ch.remark,ch.clienteleStatus,ch.type,ch.content,ch.actualFollowDate,ch.operationType as operationType," +
                "ch.nextTime,ch.imgUrl,ch.attachmentUrl,ch.leadsId,ch.clientelesId,ch.staffId,ch.staffHeadImgUrl,ch.staffName,le.channel as leadsChannel,le.fromUser as leadsfromUser" +
                " from leads_history as ch inner join leads as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId);

        querySql +=String.format(" order by ch.created desc");
        querySql +=String.format(" limit %s,%s ",query.getPageNun() * query.getPageSize(),query.getPageSize());
        List<LeadsHistoryVo> list=sqlObjectList(querySql,LeadsHistoryVo.class);
        return list;
    }

    @Override
    public Long countAll(String leadsId, LeadsHistoryQuery leadsHistoryQuery) {
        String querySql=String.format("select count(ch.id) from leads_history as ch inner join leads as le on ch.leadsId=le.id where ch.leadsId='%s'",leadsId);
        List objectArrayList = sqlSingleList(querySql);
        return  Long.valueOf(objectArrayList.get(0).toString());
    }


}
